const { pool } = require('../models/db');

async function uploadSingleData(req, res) {
  try {
    const {
      uploadTime,
      hallName,
      hallId,
      hostName,
      dataValue,
      headDataValue,
      hostHall
    } = req.body;

    if (!uploadTime || !hallName || !hostName || !dataValue) {
      return res.status(400).json({ error: '请填写所有必填字段' });
    }

    const dataScreenshot = req.files?.dataScreenshot?.[0]?.filename || null;
    const bigHeadScreenshot = req.files?.headDataScreenshot?.[0]?.filename || null;

    await pool.query(
      `INSERT INTO data_records 
       (upload_time, hall_id, host_name, data_value, big_head_value, host_hall,hall_name,
        data_screenshot, big_head_screenshot) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        uploadTime,
        hallId,
        hostName,
        parseFloat(dataValue),
        headDataValue ? parseFloat(headDataValue) : null,
        hostHall || `${hostName}-${hallName}`,
        hallName,
        dataScreenshot,
        bigHeadScreenshot
      ]
    );

    res.status(200).json({ message: '数据上传成功' });
  } catch (error) {
    console.error('单条数据上传失败:', error);
    res.status(500).json({ error: '数据上传失败，请重试' });
  }
}

async function uploadExcelData(req, res) {
  try {
    if (!req.file) {
      return res.status(400).json({ error: '请选择Excel文件' });
    }

    const XLSX = require('xlsx');
    const workbook = XLSX.readFile(req.file.path);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const data = XLSX.utils.sheet_to_json(worksheet);

    if (data.length === 0) {
      return res.status(400).json({ error: 'Excel文件中没有数据' });
    }

    const requiredFields = ['上传时间', '厅名', '主持人', '数据值'];
    console.log(data,'文件数据')
    const firstRow = data[0];
    for (const field of requiredFields) {
      if (!Object.keys(firstRow).includes(field)) {
        return res.status(400).json({ error: `Excel文件缺少必要列: ${field}` });
      }
    }

    // 日期转换函数
    function convertExcelDate(excelDate) {
      if (typeof excelDate === 'number') {
        // Excel日期序列号转换为标准日期
        const date = new Date((excelDate - 25569) * 86400 * 1000);
        return date.toISOString().slice(0, 19).replace('T', ' ');
      } else if (typeof excelDate === 'string') {
        // 如果是字符串格式，直接返回
        return excelDate;
      } else {
        // 其他情况，返回当前时间
        return new Date().toISOString().slice(0, 19).replace('T', ' ');
      }
    }

    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      
    
      for (const item of data) {

        // 根据厅名查询hall_id
        const [hallRows] = await connection.query('SELECT id FROM halls WHERE hall_name = ?', [item['厅名']]);
        const hallId = hallRows[0].id;
        if(!hallId){
          return res.status(400).json({ error: `厅名不存在: ${item['厅名']}` });
        }
        await connection.query(
          `INSERT INTO data_records 
           (upload_time, hall_id, hall_name, host_name, data_value, big_head_value, host_hall) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            convertExcelDate(item['上传时间']),
            hallId,
            item['厅名'],
            item['主持人'],
            parseFloat(item['数据值']),
            item['大头数据值'] ? parseFloat(item['大头数据值']) : null,
            item['主持-厅名'] || `${item['主持人']}-${item['厅名']}`
          ]
        );
      }
      
      await connection.commit();
      res.status(200).json({ message: `成功导入 ${data.length} 条数据` });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Excel批量上传失败:', error);
    res.status(500).json({ error: '批量上传失败，请重试' });
  }
}

async function getHalls(req, res) {
  try {
    const [rows] = await pool.query('SELECT * FROM halls');
    console.log('rows', rows);
    const halls = rows.map(row => row.hall_name);
    res.status(200).json(rows);
  } catch (error) {
    console.error('获取厅名列表失败:', error);
    res.status(500).json({ error: '获取厅名失败，请重试' });
  }
}

async function queryData(req, res) {
  try {
    const {
      startDate,
      endDate,
      hallName,
      hostName,
      page = 1,
      pageSize = 10
    } = req.query;

    let queryParams = [];
    let whereClause = [];
    
    if (startDate && endDate) {
      whereClause.push('upload_time BETWEEN ? AND ?');
      queryParams.push(`${startDate} 00:00:00`, `${endDate} 23:59:59`);
    }
    
    if (hallName && hallName !== 'all') {
      whereClause.push('hall_name = ?');
      queryParams.push(hallName);
    }
    
    if (hostName) {
      whereClause.push('host_name LIKE ?');
      queryParams.push(`%${hostName}%`);
    }
    
    const whereSql = whereClause.length ? `WHERE ${whereClause.join(' AND ')}` : '';
    
    const [countResult] = await pool.query(
      `SELECT COUNT(*) AS total FROM data_records ${whereSql}`,
      queryParams
    );
    const totalCount = countResult[0].total;
    
    const offset = (page - 1) * pageSize;
    const [data] = await pool.query(
      `SELECT * FROM data_records ${whereSql} ORDER BY upload_time DESC LIMIT ? OFFSET ?`,
      [...queryParams, parseInt(pageSize), offset]
    );
    
    res.status(200).json({
      data,
      totalCount,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      totalPages: Math.ceil(totalCount / pageSize)
    });
  } catch (error) {
    console.error('数据查询失败:', error);
    res.status(500).json({ error: '查询数据失败，请重试' });
  }
}

async function deleteData(req, res) {
  try {
    const { id } = req.params;
    if (!id) {
      return res.status(400).json({ error: '缺少记录ID' });
    }

    const [rows] = await pool.query('SELECT * FROM data_records WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ error: '记录不存在' });
    }

    await pool.query('DELETE FROM data_records WHERE id = ?', [id]);
    res.status(200).json({ message: '记录删除成功' });
  } catch (error) {
    console.error('删除数据失败:', error);
    res.status(500).json({ error: '删除失败，请重试' });
  }
}

module.exports = { 
  uploadSingleData, 
  uploadExcelData, 
  getHalls, 
  queryData,
  deleteData,
  pool
};